# coding: utf-8
from django.db import models
from lib.common import BasicManager
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
import json
import urllib
import urllib.request, urllib.error, urllib.parse
import time
from django.core.cache import cache
from lib.common import get_api_result
from apps.student.share import app_logger
from django.utils import timezone
from django.db.models import Q, Sum

class ModelManagerDelivery(BasicManager):
    # 获取vip列表
    def get_delivery_list(self, page, is_show=None, name=None, pid=None):
        from services.datamodel.student.models import mm_Student
        from services.datamodel.order.models import mm_Order, mm_Bill
        from django.db.models import Sum
        result = self.values("id", "name", "charge", "original_price", "contact", "tracking_id",
                             "convert_id", "created_at").order_by("-created_at")
        if is_show:
            result = result.filter(is_show=is_show)
        if name:
            result = result.filter(name__contains=name)
        if pid:
            result = result.filter(pid=pid)
        else:
            result = result.filter(pid=0)
        for v in result:
            contact = json.loads(v['contact'])
            u_id = [i['id'] for i in contact]
            names = mm_Student.filter(id__in=u_id).values("nick_name")
            v['headmaster_name'] = list(names)
            if pid:
                d = mm_DeliveryPvUv.filter(delivery_id=v['id']).values()
                uv_sum = 0
                pv_sum = 0
                one_uv = 0
                one_pv = 0
                for vs in d:
                    uv_sum += vs['two_level_uv']
                    pv_sum += vs['two_level_pv']
                    one_uv += vs['one_level_uv']
                    one_pv += vs['one_level_pv']
                v['uv'] = uv_sum
                v['pv'] = pv_sum
                v['one_uv'] = one_uv
                v['one_pv'] = one_pv
                v['order_sum'] = mm_Order.filter(goods_id=v['id'], status=2).count()
                v['bill_sum'] = mm_Bill.filter(student__studentchannel__launch_id=v['id'], is_pay=1).count()
                amount= mm_Bill.filter(student__studentchannel__launch_id=v['id'], is_pay=1).aggregate(amount=Sum('charge'))
                v['bill_total_amount'] = amount['amount']
        if not page:
            return {'retcode': 0, 'retlist': list(result)}
        paginator = Paginator(result, 15)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def get_delivery_index(self, p_id):
        result = self.filter(id=p_id).values("name", "is_show", "synopsis", "banner", "detail", "original_price",
                                             "charge", "source", "tracking_id", "convert_id", "payment", "contact_type",
                                             "countdown_upper", "countdown_lower", "countdown", "pay_notice",
                                             "button_color", "fictitious", "submit_text", 'types', 'pid').first()
        if result and result.get('pid'):
                result['name'] = self.get(pk=result.get('pid')).name
        return result
        # return result[0] if len(result) > 0 else None

    def up_map(self, click_id):
        url_str = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={}&secret={}".format(
            "wxe8898a8cec21dc63", "d0135d033716e5bbe48d903dbd60e824")
        result = get_api_result(url_str)
        if result.get('errcode'):
            app_logger.error(str(result))
            return False
        user_action_set_id = '1111598580'  # cache.get('user_action_set_id')
        headers = {"Content-type": "application/json"}
        if not user_action_set_id:
            post_url = "https://api.weixin.qq.com/marketing/user_action_sets/add?version=v1.0&access_token={}".format(
                result.get("access_token"))
            params = {'type': 'WEB', 'name': 'wxadtest', 'description': 'test'}
            params = json.dumps(params)
            data = bytes(params, 'utf8')
            req = urllib.request.Request(post_url, headers=headers, data=data)
            with urllib.request.urlopen(req) as f:
                resp = f.read()
                res = eval(resp)
            if res['errcode']:
                app_logger.error(str(res))
            user_action_set_id = res['data']['user_action_set_id']
            cache.set('user_action_set_id', user_action_set_id, None)
        upmap_url = "https://api.weixin.qq.com/marketing/user_actions/add?version=v1.0&access_token={}".format(
            result.get("access_token"))
        url = "http://prod.momself.cn/h5/#/h5/h5-TF-BindPhone"
        post_data = {'actions': [{'user_action_set_id': user_action_set_id, 'url': url,
                                  'action_time': int(time.time()), 'action_type': 'COMPLETE_ORDER', 'trace': {'click_id': click_id}}]}
        post_data = json.dumps(post_data)
        app_logger.error('这是传送过来的id=' + str(click_id))
        datas = bytes(post_data, 'utf8')
        res = urllib.request.Request(upmap_url, headers=headers, data=datas)
        with urllib.request.urlopen(res) as f:
            resp = f.read()
            r = eval(resp)
        app_logger.error(str(r))
        if r.get('errcode'):
            app_logger.error('这是报错=' + str(r))
        return r

    def get_launch_board(self, page, id=0, source=0, export=0):
        """获取投放渠道看板"""
        result = self.all().values('id', 'source')
        if id:
            result = result.filter(id=id)
        if source:
            result = result.filter(source=source)
        resp = []
        from services.datamodel.student.models import mm_Student
        from services.datamodel.order.models import mm_Bill
        for k in result:
            delivery_data = {'id':k['id'], 'source':k['source']}
            delivery_data['is_pay_num'] = mm_Bill.filter(student__studentchannel__launch_id=k['id'], is_pay=1).count()
            delivery_data['total_num'] = mm_Student.filter(studentchannel__launch_id=k['id']).count()
            try:
                delivery_data['conversion_rate'] = f"{delivery_data['is_pay_num'] / delivery_data['total_num']*100:.2f}%"   #保留两位小数
            except ZeroDivisionError:
                delivery_data['conversion_rate'] = '0.00%'
            resp.append(delivery_data)
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'投放渠道看板')
            w.write(0, 0, u'投放id')
            w.write(0, 1, u'投放渠道')
            w.write(0, 2, u'转化率')
            w.write(0, 3, u'成单数量')
            w.write(0, 4, u'总人数')
            excel_row = 1
            for obj in resp:
                w.write(excel_row, 0, obj['id'])
                w.write(excel_row, 1, obj['source'])
                w.write(excel_row, 2, obj['conversion_rate'])
                w.write(excel_row, 3, obj['is_pay_num'])
                w.write(excel_row, 4, obj['total_num'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        paginator = Paginator(resp, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

class ModelManagerDeliveryPvUv(BasicManager):
    #  记录pv
    def set_pv(self, level, d_id):
        from django.db import connection
        cursor = connection.cursor()
        level_dict = {1: 'one_level_pv', 2: 'two_level_pv', 3: 'three_level_pv'}
        day = timezone.now().strftime("%Y-%m-%d")
        times = timezone.now().strftime("%Y-%m-%d %H:%M:%S")
        result = self.get_or_default(day=day, delivery_id=d_id)
        if result:
            cursor.execute("update momself_delivery_pvuv set "+level_dict[level]+"="+level_dict[level]+"+1 where id="+str(result.id)+"")
        else:
            if level == 1:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',1,0,0,0,0,0)")
            if level == 2:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',0,0,1,0,0,0)")
            if level == 3:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',0,0,0,0,1,0)")
        return True

    def set_uv(self, level, d_id, types=None):
        from django.db import connection
        cursor = connection.cursor()
        level_uv_dict = {1: 'one_level_uv', 2: 'two_level_uv', 3: 'three_level_uv'}
        day = timezone.now().strftime("%Y-%m-%d")
        times = timezone.now().strftime("%Y-%m-%d %H:%M:%S")
        result = self.get_or_default(day=day, delivery_id=d_id)
        if result:
            cursor.execute("update momself_delivery_pvuv set "+level_uv_dict[level]+"="+level_uv_dict[level]+"+1 where id="+str(result.id)+"")
        else:
            if level == 1:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',0,1,0,0,0,0)")
            if level == 2:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',0,0,0,1,0,0)")
            if level == 3:
                cursor.execute("insert into momself_delivery_pvuv (`delivery_id`, `day`, `created_at`, `updated_at`, `one_level_pv`, `one_level_uv`, `two_level_pv`, `two_level_uv`, `three_level_pv`, `three_level_uv`) values("+str(d_id)+", '"+day+"', '"+times+"', '"+times+"',0,0,0,0,0,1)")
        return True

    def get_pvuv_list(self, page, start_date=None, end_date=None, delivery_id=None):
        result = self.aggregate(one_level_pv=Sum("one_level_pv"), two_level_pv=Sum("two_level_pv"),
                                three_level_pv=Sum("three_level_pv"), one_level_uv=Sum("one_level_uv"),
                                two_level_uv=Sum("two_level_uv"), three_level_uv=Sum("three_level_uv"))
        res = self.values().order_by("-created_at")
        if delivery_id:
            res = res.filter(delivery_id=delivery_id)
        if start_date:
            query = Q(created_at__range=[start_date, end_date])
            result = self.filter(query).aggregate(one_level_pv=Sum("one_level_pv"), two_level_pv=Sum("two_level_pv"),
                                                  three_level_pv=Sum("three_level_pv"), one_level_uv=Sum("one_level_uv"),
                                                  two_level_uv=Sum("two_level_uv"), three_level_uv=Sum("three_level_uv"))
            res = res.filter(query)
        paginator = Paginator(res, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'lists': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages, "retlist": result}

    def set_one_uv(self, id, user_id):
        user = cache.get('user_'+str(user_id)+'id_one'+str(id))
        if user:
            return True
        self.set_uv(1, id)
        cache.set('user_'+str(user_id)+'id_one'+str(id), 1, 3600*12)

    def set_two_uv(self, id, user_id):
        user = cache.get('user_'+str(user_id)+'id_two'+str(id))
        if user:
            return True
        self.set_uv(2, id)
        cache.set('user_'+str(user_id)+'id_two'+str(id), 1, 3600*12)

    def set_three_uv(self, id, user_id):
        user = cache.get('user_'+str(user_id)+'id_three'+str(id))
        if user:
            return True
        self.set_uv(3, id)
        cache.set('user_'+str(user_id)+'id_three'+str(id), 1, 3600*12)


class Delivery(models.Model):
    name = models.CharField("名称", max_length=50)
    is_show = models.PositiveSmallIntegerField("是否显示", default=0)
    synopsis = models.CharField("简介", default='', max_length=200)
    banner = models.TextField("轮播图", default='')
    detail = models.TextField("详情图", default='')
    original_price = models.IntegerField("原价", default=0)  # 单位：分
    charge = models.IntegerField('价格', default=0)  # 单位：分
    # 0: 默认 1:抖音 2:公众号 3:知乎 4:支付宝 5:快手 6:百度 7:小红书 8:微博 9:360 10:爱奇艺
    source = models.PositiveSmallIntegerField(default=0, verbose_name="用户来源")
    #  第三方代码==js
    tracking_id = models.CharField("抖音Tracking_id", default='', max_length=100)
    convert_id = models.CharField("抖音 convert_id", default='', max_length=100)
    payment = models.PositiveSmallIntegerField("支付方式", default=0)
    contact_type = models.PositiveSmallIntegerField("联系方式类型", default=0)  # 0:加班主任微信号 1:班主任二维码  2:公众号二维码  3:加'公众号'号码
    # [{"id": 1, "name": "a"}, {"id": 2, "name": "c"}]
    contact = models.TextField("联系方式", default='')  # 班主任微信号   二维码  公众号二维码
    # headmaster = models.CharField("班主任id", default='', max_length=100)  # 班主任id
    countdown_upper = models.PositiveSmallIntegerField("上方限时特价", default=1)  # 1:显示  0:不显示
    countdown_lower = models.PositiveSmallIntegerField("下方限时特价", default=1)  # 1:显示  0:不显示
    countdown = models.CharField("倒计时", default='', max_length=30)
    pay_notice = models.PositiveSmallIntegerField("用户购买漂浮条显示", default=1)  # 1:显示  0:不显示
    button_color = models.CharField("按钮颜色", default='', max_length=30)
    fictitious = models.IntegerField("虚拟购买人数", default=0)
    interval = models.IntegerField("间隔多少替换展示班主任", default=10)
    submit_text = models.CharField("标案提交按钮文案", default='', max_length=50)
    created_at = models.DateTimeField("添加时间", auto_now_add=True)
    updated_at = models.DateTimeField("修改时间", auto_now=True)
    types = models.CharField('抖音表单类型', max_length=20, default='')
    pid = models.PositiveSmallIntegerField("子链接", default=0)

    objects = ModelManagerDelivery()

    class Meta:
        verbose_name = '投放链接'
        verbose_name_plural = verbose_name
        db_table = "momself_delivery"

    def __str__(self):
        return self.name


class ModelManagerDynamicDelivery(BasicManager):

    def get_dy_delivery(self, page, name=None):
        """获取所有的dynamic_delivery"""
        result = self.values('id', 'name', 'tips', 'auth_uv', 'headmaster_uv', 'created_at', 'headmaster_json').order_by('-created_at')
        if name:
            result = result.filter(name__contains=name)
        for dd in result:
            if dd.get('headmaster_json'):
                dd['code_num'] = len(json.loads(dd['headmaster_json'].replace("'", '"')))  # 二维码总数
            else:
                dd['code_num'] = 0
            dd.pop('headmaster_json')
        if not page:
            return {'retcode': 0, 'retlist': list(result)}
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def get_dy_delivery_detail(self, id):
        """获取活码详情"""
        result = mm_DynamicDelivery.filter(id=id).values('id', 'name', 'tips', 'headmaster_json').first()
        if not result:
            return None
        if result['headmaster_json']:
            from services.datamodel.student.models import mm_Student
            headmaster_json = json.loads(result['headmaster_json'].replace("'", '"'))
            is_param = False                 #是否给了标签
            for hm in headmaster_json:       #该班主任在该活码id下的用户量
                hm['student_num'] = mm_Student.filter(
                    headmaster_id=hm['headmaster_id'],
                    studentchannel__launch_dynamic_id=id,
                 ).count()
                if hm.get('display_count', 0) < hm['auto_switch_num'] and not is_param:
                    hm['adding'] = 1
                    is_param = True
            # is_param 为FALSE则表示活码重新开始进人，标记放在第一个有自动转化值的活码上
            if not is_param:
                for hm in headmaster_json:
                    if hm['auto_switch_num'] > 0:
                        hm['adding'] = 1
                        is_param = True
                        break
            result['headmaster_json'] = headmaster_json
        return result

    def get_dy_board(self, page, id=0, export=0):
        """获取活码投放渠道看板"""
        result = self.all().values('id')
        if id:
            result = result.filter(id=id)
        resp = []
        from services.datamodel.student.models import mm_Student
        from services.datamodel.order.models import mm_Order, mm_Bill
        for k in result:
            delivery_data = {'id': k['id'], 'source': ''}
            delivery_data['is_pay_num'] = mm_Bill.filter(student__studentchannel__launch_dynamic_id=k['id'], is_pay=1).count()
            delivery_data['total_num'] = mm_Student.filter(studentchannel__launch_dynamic_id=k['id']).count()
            try:
                delivery_data['conversion_rate'] = f"{delivery_data['is_pay_num'] / delivery_data['total_num'] * 100:.2f}%"  # 保留两位小数
            except ZeroDivisionError:
                delivery_data['conversion_rate'] = '0.00%'
            resp.append(delivery_data)
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'活码渠道看板')
            w.write(0, 0, u'活码id')
            w.write(0, 1, u'投放渠道')
            w.write(0, 2, u'转化率')
            w.write(0, 3, u'成单数量')
            w.write(0, 4, u'总人数')
            excel_row = 1
            for obj in resp:
                w.write(excel_row, 0, obj['id'])
                w.write(excel_row, 1, obj['source'])
                w.write(excel_row, 2, obj['conversion_rate'])
                w.write(excel_row, 3, obj['is_pay_num'])
                w.write(excel_row, 4, obj['total_num'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        paginator = Paginator(resp, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def random_get_QR_code(self, headmaster_json, id):
        """获取二维码链接"""
        obj = self.filter(id=id).first()
        code = {}
        for hm_info in headmaster_json:
            if not hm_info.get('display_count', 0):
                hm_info['display_count'] = 0
            if hm_info.get('display_count', 0) < hm_info.get('auto_switch_num', 0):
                hm_info['display_count'] += 1
                code = hm_info
                break
        if not code:
            #如果code为空，则所有二维码展示次数都达到了设定次数，按顺序展示第一个设定次数不为0的班主任二维码
            for hm_info in headmaster_json:
                hm_info['display_count'] = 0
            for hm_info in headmaster_json:
                if int(hm_info['auto_switch_num']) != 0:
                    code = hm_info
                    hm_info['display_count'] += 1
                    break
        obj.headmaster_json = headmaster_json
        obj.save()
        return code

class DynamicDelivery(models.Model):
    name = models.CharField("活码名称", max_length=50)
    tips = models.CharField("自定义提示", max_length=100)
    auth_uv = models.IntegerField("auth_uv", default=0, null=True, blank=True)
    headmaster_uv = models.IntegerField("班主任uv", default=0, null=True, blank=True)
    # {'headmaster_id':0, 'code_url': '11111', "nick_name":'ytg'  ,  'auto_switch_num': 0, 'created_at': '', 'display_count':4}
    # [{班主任id   微信二维码链接   班主任姓名     自动切换指    展示次数 },] wni：用户量不存，每次调用接口实时计算
    headmaster_json = models.TextField("班主任数据组装json", null=True, blank=True)
    created_at = models.DateTimeField("添加时间", auto_now_add=True)
    updated_at = models.DateTimeField("修改时间", auto_now=True)

    objects = ModelManagerDynamicDelivery()

    class Meta:
        verbose_name = '活码投放链接'
        verbose_name_plural = verbose_name
        db_table = "momself_dnamic_delivery"

    def __str__(self):
        return self.name


class DeliveryPvUv(models.Model):
    delivery = models.ForeignKey(Delivery)
    one_level_pv = models.IntegerField("一级页面pv", default=0, blank=True)
    one_level_uv = models.IntegerField("一级页面uv", default=0, blank=True)
    two_level_pv = models.IntegerField("二级页面pv", default=0, blank=True)
    two_level_uv = models.IntegerField("二级页面uv", default=0, blank=True)
    three_level_pv = models.IntegerField("三级页面pv", default=0, blank=True)
    three_level_uv = models.IntegerField("三级页面uv", default=0, blank=True)
    day = models.CharField("日期", max_length=50)
    created_at = models.DateTimeField("添加时间", auto_now_add=True)
    updated_at = models.DateTimeField("修改时间", auto_now=True)

    objects = ModelManagerDeliveryPvUv()

    class Meta:
        verbose_name = '投放链接PvUv记录'
        verbose_name_plural = verbose_name
        db_table = "momself_delivery_pvuv"


mm_Delivery = Delivery.objects
mm_DynamicDelivery = DynamicDelivery.objects
mm_DeliveryPvUv = DeliveryPvUv.objects
